#!C:\perl\bin
use strict;
use warnings;
use lib "/perl/lib";
use Spreadsheet::ParseExcel;
use DBI;
use utf8;
use Encode;

# connection a sql
my $dbh=DBI->connect("DBI:Pg:dbname='mmdn';host=localhost","postgres","mmdn",{'RaiseError'=>1});

my $fichier=shift;
my $parser=Spreadsheet::ParseExcel->new();
my $workbook=$parser->parse("$fichier");
if (!defined $workbook){
    die $parser->error(),".\n";
}

my $worksheet;
my @dico=('A','B','C','D','E','F','G','H','I','J','K','L');

sub trim($){
	my $string = shift;
	$string =~ s/^\s+//;
	$string =~ s/\s+$//;
	if($string=~/ {2}/){
		$string=~ s/ {2}/ /;
	}
	return $string;
}

foreach($workbook->worksheets()){
    $worksheet=$_;

	my ($row_min,$row_max) = $worksheet->row_range();
	my ($col_min, $col_max) = $worksheet->col_range();
	my $cpt=0;
	my $boite1=trim($worksheet->get_name());
	my $boite="$boite1 -20";
	my $tissu;
	my $groupe;
	
	#récupération du tissu
	if($boite1=~/(BOITE|Boite|boite) {1}[A-Z]{0,3} {1}(.+)/){
		$tissu=encode_utf8($2);
		if($tissu eq encode_utf8("Banque ADN microcèbe")){$tissu="ADNg euthanasie";}
	}
	else{
		$tissu=$boite1;
	}
	
	#récupération du groupe d'organe
	if($tissu=~/ADN/){ $groupe="ADN"; }
	elsif($tissu=~/plasma/){$groupe="Sérum";}
	
	my %tab;
	my $num=1;

	for my $row ($row_min .. $row_max){ #parcours lignes
		my $sexe;
		my $code;
		my $histo;
		my $place;
		$cpt++;
		
		for my $col (1 .. $col_max){
			my $cont=$worksheet->get_cell($row,$col);
			my %infos;
			
			if($cont){
				my $temp=$cont->value();
				if($temp=~/OU/){
					my @tab1=split('OU',$temp);
					foreach (@tab1){
						%infos=();
						if($_=~/([MF])(\d{1,4})-(\d{2,4})/){ 
							$infos{'sexe'}=$1;
							$infos{'code'}=$2;
							$infos{'histo'}=$3;
							$infos{'place'}="$dico[$col-1]$cpt";
							$infos{'boite'}=$boite;
							$infos{'tissu'}=$tissu;
							$infos{'groupe'}=$groupe;
							$tab{$num}={%infos};	
						}
						elsif($_=~/([MF])(\d{1,4})/){ 
							$infos{'sexe'}=$1;
							$infos{'code'}=$2;
							$infos{'place'}="$dico[$col-1]$cpt";
							$infos{'boite'}=$boite;
							$infos{'tissu'}=$tissu;
							$infos{'groupe'}=$groupe;
							$tab{$num}={%infos};	
						}
						$num++;
					}
				}	
				elsif($temp=~/^([MF])(\d{1,4})-(\d{1,4})[ \n]*([\w .]*)/){ #ex: M399-191 ADN
					$infos{'sexe'}=$1;
					$infos{'code'}=$2;
					$infos{'histo'}=$3;
					$infos{'place'}="$dico[$col-1]$cpt";
					$infos{'boite'}=$boite;
					$infos{'groupe'}=$groupe;
					if($4 and ($boite eq 'ADNg extrait cellulaire -20' or $boite eq 'ADNg TISSUS CONGELES-PARAFFINES -20')){
						$infos{'tissu'}="$boite1 $4";
					}
					else{ $infos{'tissu'}=$tissu;}
					$tab{$num}={%infos};	
				}
				elsif($temp=~/^([MF])(\d{1,4})$/){ #ex: F488
					$infos{'sexe'}=$1;
					$infos{'code'}=$2;
					$infos{'place'}="$dico[$col-1]$cpt";
					$infos{'boite'}=$boite;
					$infos{'tissu'}=$tissu;
					$infos{'groupe'}=$groupe;
					$tab{$num}={%infos};	
				}		
				elsif($temp=~/([FM]?)(\d{1,4}[A-Z]+)-(\d{1,4})/){ #ex: F221AB-309 
					if($1 eq 'F' or $1 eq 'M'){
						$infos{'sexe'}=$1;
						$infos{'code'}=$2;
						$infos{'histo'}=$3;
					}
					else{
						$infos{'code'}=$1;
						$infos{'histo'}=$2;
					}
					$infos{'place'}="$dico[$col-1]$cpt";
					$infos{'boite'}=$boite;
					$infos{'tissu'}=$tissu;
					$infos{'groupe'}=$groupe;
					$tab{$num}={%infos};	
				}
				elsif($temp=~/[FM]?(\d{1,4}[A-Z]+)/){ #ex: F738E
					if($1 eq 'F' or $1 eq 'M'){
						$infos{'sexe'}=$1;
						$infos{'code'}=$2;
					}
					else{ $infos{'code'}=$1;}
					$infos{'place'}="$dico[$col-1]$cpt";
					$infos{'boite'}=$boite;
					$infos{'tissu'}=$tissu;
					$infos{'groupe'}=$groupe;
					$tab{$num}={%infos};	
				}
				$num++;	
			}
		}
	}
	
	#~ foreach my $i (sort keys %tab){
		#~ print "$i: ";
		#~ foreach my $k (sort keys %{$tab{$i}}){
			#~ print "$k->$tab{$i}{$k}, ";
		#~ }
		#~ print "\n";
	#~ }

	foreach my $i (sort keys %tab){
		my $code;
		my $histo;
		my $tissu;
		my $place;
		my $boite;
		my $sexe;
		my $groupe;
		
		foreach my $k (sort keys %{$tab{$i}}){
			if($k eq 'code'){ $code=$tab{$i}{$k};}
			elsif($k eq 'histo'){ $histo=$tab{$i}{$k};}
			elsif($k eq 'sexe'){ $sexe=$tab{$i}{$k};}
			elsif($k eq 'place'){ $place=$tab{$i}{$k};}
			elsif($k eq 'boite'){ $boite=$tab{$i}{$k};}
			elsif($k eq 'tissu'){ $tissu=$tab{$i}{$k};}
			elsif($k eq 'groupe'){ $groupe=$tab{$i}{$k};}
		}
		
		# ************************************* Création animal ************************************* #
		my $id_anim;
		my $sexe2;
		#si on a le code histo et que le code est du type 554
		if($histo and $code=~/^\d{1,4}$/){
			my $histo2;
			($id_anim,$histo2,$sexe2)=$dbh->selectrow_array("SELECT id_animal,code_histologique,sexe FROM Animal WHERE code_animal='$code'");
			
			if($histo2 and $histo2 ne $histo){ #si le code histo trouvé ne correspond pas au code que l'on a, on fait une nouvelle recherche
				($id_anim,$sexe2)=$dbh->selectrow_array("SELECT id_animal,sexe FROM Animal WHERE code_animal='$code' and code_histologique=$histo");
				
				if(not $id_anim){
					$dbh->do("INSERT INTO Animal(code_animal,sexe,code_histologique) VALUES ('$code','$sexe',$histo)"); 
					$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal='$code' and code_histologique=$histo");
				}
			}
		
			if(not $sexe2 and $id_anim){
				$dbh->do("UPDATE Animal SET sexe='$sexe' WHERE id_animal=$id_anim"); 
			}
			if($id_anim and not $histo2){ #ajout du code histo
				$dbh->do("UPDATE Animal SET code_histologique=$histo WHERE id_animal=$id_anim");
			}
			
			#si l'animal n'existe pas, on le créé
			if (not $id_anim){
				$dbh->do("INSERT INTO Animal(sexe,code_animal,code_histologique) VALUES ('$sexe','$code',$histo)");
				$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal='$code' and code_histologique=$histo");
			}
		}
		
		#si on a le code histo et que le code est du type 554ABU
		elsif($histo and $code=~/^\d{1,4}/){
			my $histo2;
			($id_anim,$histo2,$sexe2)=$dbh->selectrow_array("SELECT id_animal,code_histologique,sexe FROM Animal WHERE code_autre_animalerie='$code'");
			if($id_anim and not $histo2){ #ajout du code histo
				$dbh->do("UPDATE Animal SET code_histologique=$histo WHERE id_animal=$id_anim");
			}
			if($id_anim and not $sexe2 and $sexe){ #ajout du sexe
				$dbh->do("UPDATE Animal SET sexe='$sexe' WHERE id_animal=$id_anim");
			}
			
			#si l'animal n'existe pas, on le créé
			if (not $id_anim){
				if($sexe){$dbh->do("INSERT INTO Animal(sexe,code_autre_animalerie,code_histologique) VALUES ('$sexe','$code',$histo)");}
				else{$dbh->do("INSERT INTO Animal(code_autre_animalerie,code_histologique) VALUES ('$code',$histo)");}
				$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_autre_animalerie='$code' and code_histologique=$histo");
			}
		}
		#si on a que le code de type 588
		elsif(not $histo and $code=~/^\d{1,4}$/){
			($id_anim,$sexe2)=$dbh->selectrow_array("SELECT id_animal,sexe FROM Animal WHERE code_animal='$code'");
			if($id_anim and not $sexe2){ #ajout du sexe
				$dbh->do("UPDATE Animal SET sexe='$sexe' WHERE id_animal=$id_anim");
			}
			#si l'animal n'existe pas, on le créé
			if (not $id_anim){
				$dbh->do("INSERT INTO Animal(sexe,code_animal) VALUES ('$sexe','$code')");
				$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal='$code'");
			}
		}
		#si on a que le code de type 588AHD
		elsif(not $histo and $code=~/^\d{1,4}/){
			($id_anim,$sexe2)=$dbh->selectrow_array("SELECT id_animal,sexe FROM Animal WHERE code_autre_animalerie='$code'");
			if($id_anim and not $sexe2){ #ajout du sexe
				$dbh->do("UPDATE Animal SET sexe='$sexe' WHERE id_animal=$id_anim");
			}
			#si l'animal n'existe pas, on le créé
			if (not $id_anim){
				$dbh->do("INSERT INTO Animal(sexe,code_animal) VALUES ('$sexe','$code')");
				$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal='$code'");
			}
		}
		
		# ************************************* Création biobanque ************************************* #
		my $id_banque;
		my $new2=encode_utf8($boite);
		my $new=encode_utf8($tissu);
		$id_banque=$dbh->selectrow_array("SELECT id_biobanque FROM Biobanque WHERE localisation='$place' AND nom_boite='$new2' AND id_animal=$id_anim AND groupe_organe='$groupe' AND type_tissu='$new'");
		if(not $id_banque){	
			$dbh->do("INSERT INTO Biobanque(type_tissu,groupe_organe,nom_boite,localisation,id_animal) VALUES ('$new','$groupe','$new2','$place',$id_anim)"); 
		}
	}
				
	last if $_->get_name() eq 'ADNg TISSUS CONGELES-PARAFFINES';
	
}
